﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using ARR.UI.ViewModels;
using ARR.Entities;
using OfficeOpenXml;
using System.IO;
using ARR.UI.CategoriesService;
using ARR.UI.Helpers;
using System.Collections.ObjectModel;

namespace ARR.UI.Views
{
    /// <summary>
    /// Interaction logic for HistoryGenerationView.xaml
    /// </summary>
    public partial class HistoryGenerationView : UserControl
    {
        public HistoryGenerationView()
        {
            InitializeComponent();
        }

        private static string FormatCurrencyTwoDecimals = "$ #,##0.00;$ -#,##0.00";
        private static string FormatPercentageOneDecimal = @"0.0%";
        private static string FormatTwoDecimals = @"0.00";
        private static string FormatInteger = @"0";
        private static string FormatMeters = @"0.00 ""mt.""";

        private void ExportButton_Click(object sender, RoutedEventArgs e)
        {
            Microsoft.Win32.SaveFileDialog dialog = new Microsoft.Win32.SaveFileDialog();
            dialog.DefaultExt = ".xlsx";
            dialog.Filter = "Excel File (.xlsx)|*.xlsx";
            if (dialog.ShowDialog() == true)
            {
                using (ExcelPackage p = new ExcelPackage())
                {
                    p.Workbook.Properties.Author = "Tryada";
                    p.Workbook.Properties.Title = "ARR";

                    //Create a sheet
                    p.Workbook.Worksheets.Add("Category Assesment");
                    ExcelWorksheet ws = p.Workbook.Worksheets[1];
                    ws.Name = "Category Assesment";

                    #region Convertions Table

                    ws.Cells["B1"].Value = "Moneda:";
                    ws.Cells["B2"].Value = "Espacios:";
                    ws.Cells["B3"].Value = "Fuels:";

                    ws.Cells["C1"].Value = "U$S";
                    ws.Cells["C2"].Value = "Meter";
                    ws.Cells["C3"].Value = "M3";

                    ws.Cells["D1"].Value = 1;
                    ws.Cells["D2"].Value = 1;
                    ws.Cells["D3"].Value = 1;

                    #endregion

                    #region Header

                    ws.Cells["A6"].Value = "Category Code";
                    ws.Cells["B6"].Value = "Category";
                    ws.Cells["C6"].Value = "Universe";
                    ws.Cells["D6"].Value = "Role";
                    ws.Cells["E6"].Value = "Average Volume";
                    ws.Cells["F6"].Value = "Average Sales";
                    ws.Cells["G6"].Value = "Average GIM";
                    ws.Cells["H6"].Value = "Average % GIM";
                    ws.Cells["I6"].Value = "Average De-listed Volume";
                    ws.Cells["J6"].Value = "Average De-listed Sales";
                    ws.Cells["K6"].Value = "Average De-listed GIM";
                    ws.Cells["L6"].Value = "Same Store Sales Contribution";
                    ws.Cells["M6"].Value = "Same Store GIM Contribution";
                    ws.Cells["N6"].Value = "Same Store Ac. Mg. Contribution";
                    ws.Cells["O6"].Value = "Same Store Volume CY";
                    ws.Cells["P6"].Value = "Same Store Sales CY";
                    ws.Cells["Q6"].Value = "Same Store GIM CY";
                    ws.Cells["R6"].Value = "PI Diff. CY";
                    ws.Cells["S6"].Value = "Charge Off CY";
                    ws.Cells["T6"].Value = "Rebates CY";
                    ws.Cells["U6"].Value = "Actual Margin CY";
                    ws.Cells["V6"].Value = "Same Store % GIM CY";
                    ws.Cells["W6"].Value = "PI Diff. % CY";
                    ws.Cells["X6"].Value = "Charge Off % CY";
                    ws.Cells["Y6"].Value = "Rebates % CY";
                    ws.Cells["Z6"].Value = "Actual Margin % CY";

                    ws.Cells["AA6"].Value = "Same Store Inventory CY";
                    ws.Cells["AB6"].Value = "Same Store Volume PY";
                    ws.Cells["AC6"].Value = "Same Store Sales PY";
                    ws.Cells["AD6"].Value = "Same Store GIM PY";
                    ws.Cells["AE6"].Value = "PI Diff. PY";
                    ws.Cells["AF6"].Value = "Charge Off PY";
                    ws.Cells["AG6"].Value = "Rebates PY";
                    ws.Cells["AH6"].Value = "Actual Margin PY";
                    ws.Cells["AI6"].Value = "Same Store % GIM PY";
                    ws.Cells["AJ6"].Value = "PI Diff. % PY";
                    ws.Cells["AK6"].Value = "Charge Off % PY";
                    ws.Cells["AL6"].Value = "Rebates % PY";
                    ws.Cells["AM6"].Value = "Actual Margin % PY";
                    ws.Cells["AN6"].Value = "Same Store Inventory PY";
                    ws.Cells["AO6"].Value = "Volume vs. PY";
                    ws.Cells["AP6"].Value = "Sales vs. PY";
                    ws.Cells["AQ6"].Value = "GIM $ vs. PY";
                    ws.Cells["AR6"].Value = "PI Diff. vs. PY";
                    ws.Cells["AS6"].Value = "Charge Off vs. PY";
                    ws.Cells["AT6"].Value = "Rebates vs. PY";
                    ws.Cells["AU6"].Value = "Actual Margin vs. PY";
                    ws.Cells["AV6"].Value = "GIM % vs. PY";
                    ws.Cells["AW6"].Value = "Actual Margin % vs. PY";
                    ws.Cells["AX6"].Value = "Inventory vs. PY";
                    ws.Cells["AY6"].Value = "Volume vs. Avg. Store";
                    ws.Cells["AZ6"].Value = "Sales vs. Avg. Store";

                    ws.Cells["BA6"].Value = "GIM $ vs. Avg. Store";
                    ws.Cells["BB6"].Value = "GIM % vs. Avg. Store";
                    ws.Cells["BC6"].Value = "Space";
                    ws.Cells["BD6"].Value = "Gondola";
                    ws.Cells["BE6"].Value = "Transaction Area";
                    ws.Cells["BF6"].Value = "Grab & Go";
                    ws.Cells["BG6"].Value = "WIC";
                    ws.Cells["BH6"].Value = "Open Cooler";
                    ws.Cells["BI6"].Value = "Freezer";
                    ws.Cells["BJ6"].Value = "Forecourt";
                    ws.Cells["BK6"].Value = "Other Exhibition";
                    ws.Cells["BL6"].Value = "Space share";
                    ws.Cells["BM6"].Value = "Annual Stock Turn";
                    ws.Cells["BN6"].Value = "Inventory Days";
                    ws.Cells["BO6"].Value = "GMROI";
                    ws.Cells["BP6"].Value = "Sales/ Linear mt.";
                    ws.Cells["BQ6"].Value = "GMROS";
                    ws.Cells["BR6"].Value = "Hurdle Rate";
                    ws.Cells["BS6"].Value = "Average Price";
                    ws.Cells["BT6"].Value = "AM - Skus w/Sales";
                    ws.Cells["BU6"].Value = "AM - New Skus";
                    ws.Cells["BV6"].Value = "AM - TOTAL Skus";
                    ws.Cells["BW6"].Value = "Skus in Sales Files";
                    ws.Cells["BX6"].Value = "De-list Proposal";
                    ws.Cells["BY6"].Value = "After RR";
                    ws.Cells["BZ6"].Value = "10% Sales";

                    ws.Cells["CA6"].Value = "20% Sales";
                    ws.Cells["CB6"].Value = "30% Sales";
                    ws.Cells["CC6"].Value = "40% Sales";
                    ws.Cells["CD6"].Value = "50% Sales";
                    ws.Cells["CE6"].Value = "60% Sales";
                    ws.Cells["CF6"].Value = "70% Sales";
                    ws.Cells["CG6"].Value = "80% Sales";
                    ws.Cells["CH6"].Value = "90% Sales";
                    ws.Cells["CI6"].Value = "100% Sales";
                    ws.Cells["CJ6"].Value = "# SKUs in the bottom 5%";
                    ws.Cells["CK6"].Value = "# SKUs below HR";
                    ws.Cells["CL6"].Value = "% SKUs with 80% Sales";
                    ws.Cells["CM6"].Value = "Active Categories";
                    ws.Cells["CN6"].Value = "Categories w/Inv. analysis";
                    ws.Cells["CO6"].Value = "Pais";
                    ws.Cells["CP6"].Value = "Range";

                    #endregion

                    #region Data

                    #region Query Total Store

                    HistoryDetail totalStore = null;

                    //Busco el registro para TOTAL STORE.
                    ObservableCollection<HistoryDetail> details = this.HistoryGV.ItemsSource as ObservableCollection<HistoryDetail>;
                    if (details != null && details.Any())
                    {
                        totalStore = details.Where(x => x.Category == "TOTAL STORE").FirstOrDefault();
                    }

                    #endregion

                    List<Category> categories = new List<Category>();
                    CategoriesServiceClient service = new CategoriesServiceClient();
                    try
                    {
                        categories = service.GetAll();
                        service.Close();
                    }
                    catch
                    {
                        service.Abort();
                        throw;
                    }

                    int row = 7;
                    foreach (HistoryDetail historyRow in this.HistoryGV.Items)
                    {
                        var cat = categories.SingleOrDefault(x => x.Id == historyRow.CategoryId);

                        ws.Cells[row, 1].Value = cat != null ? cat.Code : ""; //A
                        ws.Cells[row, 2].Value = historyRow.Category; //B
                        ws.Cells[row, 3].Value = historyRow.Universe; //C
                        ws.Cells[row, 4].Value = historyRow.Role; //D
                        ws.Cells[row, 5].Value = historyRow.AverageVolume; //E
                        ws.Cells[row, 5].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 6].Value = historyRow.AverageSales; //F
                        ws.Cells[row, 6].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 7].Value = historyRow.AverageGIM; //G
                        ws.Cells[row, 7].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 8].Value = historyRow.AverageGIMP; //H
                        ws.Cells[row, 8].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 9].Value = historyRow.AverageDelistedVolume; //I
                        ws.Cells[row, 9].Style.Numberformat.Format = FormatInteger;

                        ws.Cells[row, 10].Value = historyRow.AverageDelistedSales; //J
                        ws.Cells[row, 10].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 11].Value = historyRow.AverageDelistedGIM; //K
                        ws.Cells[row, 11].Style.Numberformat.Format = FormatCurrencyTwoDecimals;

                        if (totalStore.SS_SalesCY.HasValue && totalStore.SS_SalesCY != 0)
                        {
                            ws.Cells[row, 12].Value = (historyRow.SS_SalesCY ?? 0) / totalStore.SS_SalesCY; //L
                            ws.Cells[row, 12].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        }
                        else
                        {
                            ws.Cells[row, 12].Value = null;
                        }

                        if (totalStore.SS_GIMCY.HasValue && totalStore.SS_GIMCY != 0)
                        {
                            ws.Cells[row, 13].Value = (historyRow.SS_GIMCY ?? 0) / totalStore.SS_GIMCY; //M
                            ws.Cells[row, 13].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        }
                        else
                        {
                            ws.Cells[row, 13].Value = null;
                        }

                        if (totalStore.SS_ActualMarginCY.HasValue && totalStore.SS_ActualMarginCY != 0)
                        {
                            ws.Cells[row, 14].Value = (historyRow.SS_ActualMarginCY ?? 0) / totalStore.SS_ActualMarginCY; //N
                            ws.Cells[row, 14].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        }
                        else
                        {
                            ws.Cells[row, 14].Value = null;
                        }

                        ws.Cells[row, 15].Value = historyRow.SS_VolumeCY; //O
                        ws.Cells[row, 15].Style.Numberformat.Format = FormatInteger;

                        ws.Cells[row, 16].Value = historyRow.SS_SalesCY; //P
                        ws.Cells[row, 16].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 17].Value = historyRow.SS_GIMCY; //Q
                        ws.Cells[row, 17].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 18].Value = historyRow.SS_PIDiffCY; //R
                        ws.Cells[row, 18].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 19].Value = historyRow.SS_ChargeOffCY; //S
                        ws.Cells[row, 19].Style.Numberformat.Format = FormatCurrencyTwoDecimals;

                        ws.Cells[row, 20].Value = historyRow.SS_RebatesCY; //T
                        ws.Cells[row, 20].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 21].Value = historyRow.SS_ActualMarginCY; //U
                        ws.Cells[row, 21].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 22].Value = historyRow.SS_GIMPCY; //V
                        ws.Cells[row, 22].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 23].Value = historyRow.SS_PIDiffPCY; //W
                        ws.Cells[row, 23].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 24].Value = historyRow.SS_ChargeOffPCY; //X
                        ws.Cells[row, 24].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 25].Value = historyRow.SS_RebatesPCY; //Y
                        ws.Cells[row, 25].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 26].Value = historyRow.SS_ActualMarginPCY; //Z
                        ws.Cells[row, 26].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 27].Value = historyRow.SS_InventoryCY; //AA
                        ws.Cells[row, 27].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 28].Value = historyRow.SS_VolumePY; //AB
                        ws.Cells[row, 28].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 29].Value = historyRow.SS_SalesPY; //AC
                        ws.Cells[row, 29].Style.Numberformat.Format = FormatCurrencyTwoDecimals;

                        ws.Cells[row, 30].Value = historyRow.SS_GIMPY; //AD
                        ws.Cells[row, 30].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 31].Value = historyRow.SS_PIDiffPY; //AE 
                        ws.Cells[row, 31].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 32].Value = historyRow.SS_ChargeOffPY; //AF
                        ws.Cells[row, 32].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 33].Value = historyRow.SS_RebatesPY; //AG
                        ws.Cells[row, 33].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 34].Value = historyRow.SS_ActualMarginPY; //AH
                        ws.Cells[row, 34].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 35].Value = historyRow.SS_GIMPPY; //AI
                        ws.Cells[row, 35].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 36].Value = historyRow.SS_PIDiffPPY; //AJ
                        ws.Cells[row, 36].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 37].Value = historyRow.SS_ChargeOffPPY; //AK
                        ws.Cells[row, 37].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 38].Value = historyRow.SS_RebatesPPY; //AL
                        ws.Cells[row, 38].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 39].Value = historyRow.SS_ActualMarginPPY; //AM
                        ws.Cells[row, 39].Style.Numberformat.Format = FormatPercentageOneDecimal;

                        ws.Cells[row, 40].Value = historyRow.SS_InventoryPY; //AN
                        ws.Cells[row, 40].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 41].Value = historyRow.SS_VolumeVsPY; //AO
                        ws.Cells[row, 41].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 42].Value = historyRow.SS_SalesVsPY; //AP
                        ws.Cells[row, 42].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 43].Value = historyRow.SS_GIMVsPY; //AQ
                        ws.Cells[row, 43].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 44].Value = historyRow.SS_PIDiffVsPY; //AR
                        ws.Cells[row, 44].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 45].Value = historyRow.SS_ChargeOffVsPY; //AS
                        ws.Cells[row, 45].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 46].Value = historyRow.SS_RebatesVsPY; //AT
                        ws.Cells[row, 46].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 47].Value = historyRow.SS_ActualMarginVsPY; //AU
                        ws.Cells[row, 47].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 48].Value = historyRow.SS_GIMPVsPY; //AV
                        ws.Cells[row, 48].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 49].Value = historyRow.SS_ActualMarginPVsPY; //AW
                        ws.Cells[row, 49].Style.Numberformat.Format = FormatPercentageOneDecimal;

                        ws.Cells[row, 50].Value = historyRow.SS_InventoryVsPY; //AX
                        ws.Cells[row, 50].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 51].Value = historyRow.SS_VolumeVsAverage; //AY
                        ws.Cells[row, 51].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 52].Value = historyRow.SS_SalesVsAverage; //AZ
                        ws.Cells[row, 52].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 53].Value = historyRow.SS_GIMVsAverage; //BA
                        ws.Cells[row, 53].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 54].Value = historyRow.SS_GIMPVsAverage; //BB
                        ws.Cells[row, 54].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 55].Value = historyRow.Space; //BC
                        ws.Cells[row, 55].Style.Numberformat.Format = FormatMeters;
                        ws.Cells[row, 56].Value = historyRow.Gondola; //BD
                        ws.Cells[row, 56].Style.Numberformat.Format = FormatMeters;
                        ws.Cells[row, 57].Value = historyRow.TransactionArea; //BE
                        ws.Cells[row, 57].Style.Numberformat.Format = FormatMeters;
                        ws.Cells[row, 58].Value = historyRow.GrabAndGo; //BF
                        ws.Cells[row, 58].Style.Numberformat.Format = FormatMeters;
                        ws.Cells[row, 59].Value = historyRow.WIC; //BG
                        ws.Cells[row, 59].Style.Numberformat.Format = FormatMeters;

                        ws.Cells[row, 60].Value = historyRow.OpenCooler; //BH
                        ws.Cells[row, 60].Style.Numberformat.Format = FormatMeters;
                        ws.Cells[row, 61].Value = historyRow.Freezer; //BI
                        ws.Cells[row, 61].Style.Numberformat.Format = FormatMeters;
                        ws.Cells[row, 62].Value = historyRow.Forecourt; //BJ
                        ws.Cells[row, 62].Style.Numberformat.Format = FormatMeters;
                        ws.Cells[row, 63].Value = historyRow.OtherExhibition; //BK
                        ws.Cells[row, 63].Style.Numberformat.Format = FormatMeters;

                        if (historyRow.Space.HasValue && totalStore.Space.HasValue && totalStore.Space != 0)
                        {
                            ws.Cells[row, 64].Value = historyRow.Space / totalStore.Space; //BL
                            ws.Cells[row, 64].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        }
                        else
                        {
                            ws.Cells[row, 64].Value = null; //BL
                        }

                        ws.Cells[row, 65].Value = historyRow.AnnualStockTurn; //BM
                        ws.Cells[row, 65].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 66].Value = historyRow.InventoryDays; //BN
                        ws.Cells[row, 66].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 67].Value = historyRow.GMROI; //BO
                        ws.Cells[row, 67].Style.Numberformat.Format = FormatTwoDecimals;
                        ws.Cells[row, 68].Value = historyRow.SalesVsLinearSpace; //BP
                        ws.Cells[row, 68].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 69].Value = historyRow.GMROS; //BQ
                        ws.Cells[row, 69].Style.Numberformat.Format = FormatTwoDecimals;

                        ws.Cells[row, 70].Value = historyRow.HurdleRateRatio; //BR
                        ws.Cells[row, 70].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 71].Value = historyRow.AveragePrice; //BS
                        ws.Cells[row, 71].Style.Numberformat.Format = FormatCurrencyTwoDecimals;
                        ws.Cells[row, 72].Value = historyRow.SKUsWithSales; //BT
                        ws.Cells[row, 72].Style.Numberformat.Format = FormatInteger;

                        ws.Cells[row, 73].Value = historyRow.NewSKUs; //BU
                        ws.Cells[row, 73].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 74].Value = historyRow.TotalSKUs; //BV
                        ws.Cells[row, 74].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 75].Value = historyRow.SKUsInSalesFiles; //BW
                        ws.Cells[row, 75].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 76].Value = historyRow.DelistProposal; //BX
                        ws.Cells[row, 76].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 77].Value = historyRow.SKUsAfterRR; //BY
                        ws.Cells[row, 77].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 78].Value = historyRow.Sales10; //BZ
                        ws.Cells[row, 78].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 79].Value = historyRow.Sales20; //CA
                        ws.Cells[row, 79].Style.Numberformat.Format = FormatInteger;

                        ws.Cells[row, 80].Value = historyRow.Sales30; //CB
                        ws.Cells[row, 80].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 81].Value = historyRow.Sales40; //CC
                        ws.Cells[row, 81].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 82].Value = historyRow.Sales50; //CD
                        ws.Cells[row, 82].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 83].Value = historyRow.Sales60; //CE
                        ws.Cells[row, 83].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 84].Value = historyRow.Sales70; //CF
                        ws.Cells[row, 84].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 85].Value = historyRow.Sales80; //CG
                        ws.Cells[row, 85].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 86].Value = historyRow.Sales90; //CH
                        ws.Cells[row, 86].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 87].Value = historyRow.Sales100; //CI
                        ws.Cells[row, 87].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 88].Value = historyRow.Bottom5; //CJ
                        ws.Cells[row, 88].Style.Numberformat.Format = FormatInteger;
                        ws.Cells[row, 89].Value = historyRow.HurdleRate; //CK
                        ws.Cells[row, 89].Style.Numberformat.Format = FormatInteger;

                        ws.Cells[row, 90].Value = historyRow.SKUsWith80SalesP; //CL
                        ws.Cells[row, 90].Style.Numberformat.Format = FormatPercentageOneDecimal;
                        ws.Cells[row, 91].Value = historyRow.ActiveCategories; //CM
                        ws.Cells[row, 92].Value = historyRow.CategoriesWithInvAnalysis; //CN
                        ws.Cells[row, 93].Value = WorkspaceData.Instance.Country.Name; //CO
                        ws.Cells[row, 94].Value = WorkspaceData.Instance.RangeReview.Title; //CP

                        row++;
                    }

                    #endregion

                    #region Styles

                    ws.Cells.Style.Font.Size = 11;
                    ws.Cells.Style.Font.Name = "Calibri";
                    ws.View.ShowGridLines = false;
                    ws.defaultColWidth = 14;
                    ws.Column(2).Width = 36;
                    ws.View.FreezePanes(7, 3);
                    ws.View.ZoomScale = 90;

                    ExcelHelper.SetBorders(ws.Cells[6, 1, row, 94], OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                    ExcelHelper.SetBorders(ws.Cells[1, 2, 3, 4], OfficeOpenXml.Style.ExcelBorderStyle.Thin);

                    ws.Cells["A6:CP6"].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    ws.Cells["A6:CP6"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    ws.Cells["A6:CP6"].Style.WrapText = true;

                    ws.Cells["A6:CP6"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws.Cells["A6:D6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(242, 242, 242));
                    ws.Cells["E6:H6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(184, 204, 228));
                    ws.Cells["I6:K6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(220, 230, 241));
                    ws.Cells["L6:AA6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(218, 150, 148));
                    ws.Cells["AB6:AN6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(230, 184, 183));
                    ws.Cells["AO6:AX6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(252, 213, 180));
                    ws.Cells["AY6:BB6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(253, 233, 217));
                    ws.Cells["BC6:BL6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(177, 160, 199));
                    ws.Cells["BM6:BS6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(196, 189, 151));
                    ws.Cells["BT6:BY6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(196, 215, 155));
                    ws.Cells["BZ6:CL6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(220, 230, 241));
                    ws.Cells["CM6:CN6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(252, 213, 180));
                    ws.Cells["CO6:CP6"].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(242, 242, 242));

                    //TotalStore
                    int totalStoreRow = details.IndexOf(totalStore) + 7;
                    ws.Cells[string.Format("A{0}:CP{0}", totalStoreRow)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws.Cells[string.Format("A{0}:CP{0}", totalStoreRow)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(196, 215, 155));
                    ws.Cells[string.Format("A{0}:CP{0}", totalStoreRow)].Style.Font.Bold = true;


                    #endregion

                    //Generate the xslx
                    File.WriteAllBytes(dialog.FileName, p.GetAsByteArray());


                }
            }
        }

    }
}
